package com.kzhkwkt.sugoieditor;

import java.util.HashMap;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

public class Data extends SQLiteOpenHelper {

	private final String TAG = "Data";
	private static final String DB_NAME = "sugoi_editor.db";
	private static final String TABLE_NAME = "data";
	private static final int DB_VERSION = 1;

	public static final String TITLE = "title";
	public static final String CONTENTS = "contents";
	public static final String CREATED = "created";
	public static final String MODIFIED = "modified";

	private SQLiteDatabase db;
	private static Data data = null;

	/**
	 * コンストラクタ
	 * @param context
	 */
	private Data(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		db = getWritableDatabase();
	}

	public static Data getInstance(Context con) {
		if(data == null) {
			data = new Data(con);
		}
		return data;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.beginTransaction();
		try {
			String sql = "CREATE TABLE " + TABLE_NAME + "(" +
						BaseColumns._ID		+ " INTEGER PRIMARY KEY AUTOINCREMENT, " +
						TITLE          		+ " TEXT, " +
						CONTENTS			+ " TEXT, " +
						CREATED				+ " TEXT, " +
						MODIFIED			+ " int  )" ;

			db.execSQL(sql);
			db.setTransactionSuccessful();
			Log.i(TAG, "Database is created");
		}
		catch(SQLException e) {
			Log.e(TAG, e.getMessage());
		}
		finally {
			db.endTransaction();
		}
	}

	@Override
	public void close() {
		db.close();
	}

	/**
	 * 引数で指定されたIDのファイルを返す
	 * @return テーブルから取得したファイルオブジェクト
	 */
	public ActiveFile getFileById(int id) {
		ActiveFile file = new ActiveFile();
		String sql = "SELECT * FROM " + TABLE_NAME + " WHERE " + BaseColumns._ID + " = " + id + " LIMIT 1";
		try {
			Cursor c = rawQuery(sql);
			if(c.getCount() < 1) {
				return null;
			}
			c.moveToFirst();
			file.setId(c.getInt(0));
			file.setTitle(c.getString(1));
			file.setContents(c.getString(2));
//			file.setCreated(c.getShort(3));
//			file.setModified(c.getString(4));
			c.close();
		}
		catch(Exception e) {
			Log.e(TAG, "SQL ERROR", e);
		}
		return file;
	}

	public HashMap<Integer, String> getList() {
		HashMap<Integer, String> list = new HashMap<Integer, String>();
		String sql = "SELECT " + BaseColumns._ID + ", " + TITLE + " FROM " + TABLE_NAME ;
		try {
			Cursor c = rawQuery(sql);
			if(c.getCount() < 1) {
				return null;
			}
			c.moveToFirst();
			while(!c.isAfterLast()) {
				list.put(c.getInt(0), c.getString(1));
				c.moveToNext();
			}
			c.close();
		}
		catch(Exception e) {
			Log.e(TAG, "SQL ERROR", e);
			return null;
		}
		return list;

	}

	public boolean save(ActiveFile file) {
		if(file != null && file.getId() != 0) {
			return update(file);
		}
		return insert(file);
	}

	/**
	 * SQLを実行し、テーブルの値を更新する
	 * @return 更新成功 : true / それ以外はfalse
	 */
	public boolean update(ActiveFile file) {
		String sql = "UPDATE " + TABLE_NAME + " SET " + TITLE + " = '" + file.getTitle() + "', " + CONTENTS + " = '" + file.getContents() + "' WHERE " + BaseColumns._ID + " = " + file.getId();
		try {
			execSQL(sql);
		}
		catch(SQLException e) {
			Log.e(TAG, e.getMessage());
			return false;
		}
		return true;
	}

	public boolean insert(ActiveFile file) {
		String sql = "INSERT INTO " + TABLE_NAME + "(" + TITLE + ", " + CONTENTS + ") VALUES('" + file.getTitle() + "', '" + file.getContents() + "')";
		try {
			execSQL(sql);
		}
		catch(SQLException e) {
			Log.e(TAG, e.getMessage());
			return false;
		}
		return true;
	}

	public int count() {
		String sql = "SELECT count(" + BaseColumns._ID + ") FROM " + TABLE_NAME;
		Cursor c = rawQuery(sql);
		c.moveToFirst();
		return c.getInt(0);
	}

	Cursor rawQuery(String sql) {
		Log.i(TAG, sql);
		return db.rawQuery(sql, null);
	}

	void execSQL(String sql) {
		Log.i(TAG, sql);
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}
}
